<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');
require_once ('Entity/Skill_Categories_Entity.php');
class Skill_Categories_Model extends Skill_Categories_Entity
{
    public function __construct()
    {
        parent::__construct();
    }
    
    public function getCategorybyAlias($alias)
    {
        $sql = "SELECT c.id 
            FROM skill_categories c
            WHERE c.alias = ? ";
        $query = $this->db->query($sql, array($alias));
       
        if ($query->num_rows() > 0) {
            return $query->row();
        }
        return array();
    }
    
    public function upHit($id = 0)
    {
        if (!isset($_SESSION['skill_categories'][$id])) {
            $_SESSION['skill_categories'][$id] = $id;
            $sql = "UPDATE skill_categories SET hit = hit + 1 WHERE id = ?";
            $this->db->query($sql, array($id));
        }
        
    }
    
		
    public function getCategorybyId($cid = 0)
    {
        $sql = "SELECT  v.title as vtitle, m.title as mtitle, m.alias as malias, c.* 
            FROM skill_videos v 
            LEFT OUTER JOIN skill_video_categories vc ON vc.vid = v.id
            LEFT OUTER JOIN skill_categories c ON c.id = vc.cid 
            LEFT OUTER JOIN skill_menus m ON c.mid = m.id
            WHERE c.id = ?";
        $query = $this->db->query($sql, array($cid));
        if ($query->num_rows() > 0) {
            return $query->row();
        }
        return array();
    }
/*



*/
    public function getCategory_ByMenu($mid = 0, $ofset = 0, $limit = 10, $ord = 0, $cid = 0)
    {
        $sql = "SELECT *, COUNT(vid) as num_video from (
                  SELECT c.id, c.title, c.alias, c.created, c.summary, c.image, v.id as vid 
            	   FROM skill_videos v 
                    INNER JOIN skill_video_categories vc ON vc.vid = v.id
                    INNER JOIN skill_categories c ON c.id = vc.cid 
                    INNER JOIN skill_menus m ON c.mid = m.id
            	WHERE c.mid = $mid AND c.status = 1 AND v.status = 1 ";
        if ($cid != 0) {
            $sql .= " AND c.id != ? ";
        }
        $sql .= "
            ) temp group by id";
        if ($ord == 1) {
            $sql .= "    
                ORDER BY created DESC ";
        } else {
            $sql .= "    
                ORDER BY RAND() ";
        }    
        $sql .= "
            LIMIT $ofset, $limit";

        if ($cid != 0) {
            $query = $this->db->query($sql, array($cid));
        } else {
            $query = $this->db->query($sql);
        }
         
        return $query->result();
    }
    
    public function TrimInArray($arr)
    {
        $ar = array();
        $i = 1;
        foreach ($arr as $a) {
            if ($i > 6) break;
            $i++;
            $ar[] = $a;
        }
        return $ar;
    }
    
    public function cTotal($mid = 1)
    {
        $sql = "SELECT id FROM skill_categories WHERE status = 1 AND mid = $mid";
        $query = $this->db->query($sql);
        return $query->num_rows();
    }
    
    public function frontTop()
    {
        $sql = "SELECT c.id, c.title, c.alias, c.image, vc.vid, v.title as vtitle, 
            v.alias as valias, m.alias as malias
                    FROM skill_categories c
                    INNER JOIN skill_video_categories vc ON c.id = vc.cid
                    INNER JOIN skill_videos v ON v.id = vc.vid
                    INNER JOIN skill_menus m ON m.id = c.mid
                    WHERE c.top = 1
                    ORDER BY v.hit desc, c.created DESC
            ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function blockCoursesSoft()
    {
        $f = root_dir().'libs/courses.xml';
        $data = simplexml_load_file($f);
        $arr = array();
        foreach ($data as $row) {
            if ($row->category == 1) {
                $arr[] = $row;
            }
        }
        return $arr;
    }

    public function blockCoursesEnglish()
    {
        $f = root_dir().'libs/courses.xml';
        $data = simplexml_load_file($f);
        $arr = array();
        foreach ($data as $row) {
            if ($row->category == 2) {
                $arr[] = $row;
            }
        }
        return $arr;
    }
    /**
     * Admin model
     */
    public function dataSort($field, $order)
    {
        $xOrder = $order == 'asc' ? 'desc' : 'asc';
        $img = $order == 'desc' ? '<img src=\'/data/admin/down.jpg\' />' : ' <img src=\'/data/admin/up.jpg\' />';
        $url = anchor('skill_categories/sort/' . $field . '/' . $xOrder, t($field) . $img);
        $_SESSION['sm_sort_skill_categories'] = array(
            'field' => $field,
            'order' => $order,
            'url' => $url);
    }

    public function dataIndex($target)
    {
        $numshow = 25;
        $this->db->select('skill_categories.id, skill_categories.title, skill_categories.created, skill_categories.status, skill_categories.hit, skill_categories.top, skill_categories.courses, skill_menus.title as mid_type');
        $this->db->from('skill_categories');

        $this->db->join('skill_menus', 'skill_categories.mid = skill_menus.id');
        
        if ($this->session->userdata('sm_filter_skill_menus')) {
            $this->db->where('skill_categories.mid', $this->session->userdata('sm_filter_skill_menus'));
        }

        if ($this->session->userdata('sm_find_skill_categories')) {
            $this->db->like('skill_categories.title', $this->session->userdata('sm_find_skill_categories'));
        }
       

        if (isset($_SESSION['sm_sort_skill_categories'])) {
            $sort = $_SESSION['sm_sort_skill_categories'];
            $this->db->order_by('skill_categories.' . $sort['field'], $sort['order']);
        } else {
            $this->db->order_by('skill_categories.created', 'desc');
        }
        $this->db->limit($numshow, $target);
        
        $query = $this->db->get();
        
        $paging = paging_admin('skill_categories/index', $this->_total(), $numshow, 4);
        return array(
            'query' => $query,
            'paging' => $paging,
            'fil_skill_menus' => $this->Filter_skill_menus(),
            'fil_skill_categories_top' => $this->Filter_skill_categories_top(),
            'formFind' => $this->formFind());
    }

    private function cbo_skill_menus($id = 0)
    {
        $this->load->helper("form");
        $query = $this->db->get("skill_menus");
        foreach ($query->result() as $row) {
            $options[$row->id] = $row->title;
        }
        $sl = $id == 0 ? set_value("mid") : $id;
        return form_dropdown("mid", $options, $sl);
    }

    private function cbo_skill_categories_top($id = 0)
    {
        $this->load->helper("form");
        $query = $this->db->get("skill_categories_top");
        $options[-1] = 'None';
        foreach ($query->result() as $row) {
            $options[$row->id] = $row->title;
        }
        $sl = $id == 0 ? set_value("top") : $id;
        return form_dropdown("top", $options, $sl);
    }

    private function _total()
    {
        return $this->db->count_all_results("skill_categories");
    }

    private function _checkskill_categoriesById($id)
    {
        $query = $this->db->get_where("skill_categories", array("id" => $id));
        if ($query->num_rows() > 0)
            return true;
        else {
            notify_set_messages(notify_atten(t("Record không tồn tại")), "skill_categories");
            return false;
        }
    }

    private function formFind()
    {
        $this->load->helper('form');
        $attributes = array('name' => 'frmFind', 'id' => 'frmFind');
        $f = form_open(site_url('skill_categories/find'), $attributes);
        $data = array(
            'name' => 'txt_find',
            'id' => 'txt_find',
            'value' => '',
            'style' => 'width:200px');
        $f .= form_input($data);
        $s = array(
            'name' => 'submit',
            'value' => t('Find'),
            'class' => 'ibutton green',
            'style' => 'margin-left:10px');
        $f .= form_submit($s);
        if ($this->session->userdata('sm_find_skill_categories')) {
            $r = array(
                'name' => 'btnReset',
                'value' => 'Reset',
                'class' => 'ibutton red',
                'style' => 'margin-left:10px');
            $f .= form_submit($r);
        }
        $f .= form_close();
        return $f;
    }

    private function Filter_skill_menus()
    {
        $this->load->helper('form');
        $query = $this->db->get('skill_menus');
        foreach ($query->result() as $row) {
            $options[$row->id] = $row->title;
        }
        $id = !$this->session->userdata('sm_filter_skill_menus') ? 0 : $this->session->userdata('sm_filter_skill_menus');
        $attributes = array('name' => 'frmFilterskill_menus', 'id' => 'frmFilterskill_menus');
        $f = form_open(site_url('skill_categories/filterskill_menus'), $attributes);
        $f .= form_dropdown('mid', $options, $id);
        $data = array(
            'name' => 'btnFilterskill_menus',
            'class' => 'ibutton green',
            'style' => 'margin-left:10px',
            'value' => t('Filter'));
        $f .= form_submit($data);
        if ($this->session->userdata('sm_filter_skill_menus')) {
            $r = array(
                'name' => 'btnReset',
                'class' => 'ibutton red',
                'style' => 'margin-left:10px',
                'value' => 'Reset');
            $f .= form_submit($r);
        }
        $f .= form_close();
        return $f;
    }

    private function Filter_skill_categories_top()
    {
        $this->load->helper('form');
        $query = $this->db->get('skill_categories_top');
        $options[-1] = 'None';
        foreach ($query->result() as $row) {
            $options[$row->id] = $row->title;
        }
        $id = !$this->session->userdata('sm_filter_skill_categories_top') ? 0 : $this->session->userdata('sm_filter_skill_categories_top');
        $attributes = array('name' => 'frmFilterskill_categories_top', 'id' => 'frmFilterskill_categories_top');
        $f = form_open(site_url('skill_categories/filterskill_categories_top'), $attributes);
        $f .= form_dropdown('top', $options, $id);
        $data = array(
            'name' => 'btnFilterskill_categories_top',
            'class' => 'ibutton green',
            'style' => 'margin-left:10px',
            'value' => t('Filter'));
        $f .= form_submit($data);
        if ($this->session->userdata('sm_filter_skill_categories_top')) {
            $r = array(
                'name' => 'btnReset',
                'class' => 'ibutton red',
                'style' => 'margin-left:10px',
                'value' => 'Reset');
            $f .= form_submit($r);
        }
        $f .= form_close();
        return $f;
    }

    public function dataEdit($id)
    {
        if (!$this->_checkskill_categoriesById($id))
            return redirect(site_url('skill_categories'));
        $this->load->helper('editor');
        $query = $this->db->get_where('skill_categories', array('id' => $id));
        return array(
            'query' => $query,
            'cbo_skill_menus' => $this->cbo_skill_menus($query->row('mid')),
            'cbo_skill_categories_top' => $this->cbo_skill_categories_top($query->row('top')),
            );
    }

    public function dataAdd()
    {
        $this->load->helper('editor');
        return array(
            'cbo_skill_menus' => $this->cbo_skill_menus(),
            'cbo_skill_categories_top' => $this->cbo_skill_categories_top(),
            );
    }

    public function checkForm()
    {
        $this->form_validation->set_rules('title', t('title'), 'trim|required');
        $this->form_validation->set_rules('summary', t('summary'), 'trim|required');
        $this->form_validation->set_rules('contents', t('contents'), 'trim|required');
        $this->form_validation->set_rules('created', t('created'), 'trim|required');
        $this->form_validation->set_rules('keyword', t('keyword'), 'trim|required');
        $this->form_validation->set_rules('description', t('description'), 'trim|required');
        $this->form_validation->set_rules('mid', t('mid'), 'trim|required|integer');
        return $this->form_validation->run();
    }

    public function doUpdate()
    {
        $this->load->helper('text');
        $title = $this->input->post('title');
        $time = strtotime($this->input->post('created'));
        $this->setTitle($title);
        $alias = isset($_POST['alias']) ? $_POST['alias'] : iurl_title($title).'-'.date('y-m-d', $time);
        $this->setAlias($alias);
        
        
        $this->setImage($this->input->post('image'));
        $this->setSummary($this->input->post('summary'));
        $this->setContents($this->input->post('contents'));
        $this->setStatus($this->input->post('status'));
        $this->setKeyword($this->input->post('keyword'));
        $this->setDescription($this->input->post('description'));
        
        $top = isset($_POST['top']) ? $this->input->post('top') : 0;
        $this->setTop($top);
        //$courses = isset($_POST['courses']) ? 1 : 0;

        //$this->setCourses($courses);
        $this->setMid($this->input->post('mid'));
        
        $this->setCreated($time);
        if (isset($_POST['idx'])) {
            $id = decrypt($_POST['idx']);
            $this->setId($id);
            $this->setChanged(time());
        }
        return $this->save();
    }
    
    public function checkVideoInCategory($cid)
    {
        $sql = "SELECT id FROM skill_video_categories WHERE cid = ?";
        $query = $this->db->query($sql, array($cid));
        if ($query->num_rows() > 0) {
            return true;
        }
        return false;
    }
    
}
